import sqlite3

class DatabaseItem(object):
    @classmethod
    def load(cls, cursor):
        db_instances = cursor.execute("""SELECT %s from %s;""" % (",".join(cls.FIELDS), cls.TABLE))
        py_instances = []
        for db_instance in db_instances:
            py_instance = cls()
            for rank, name in enumerate(cls.FIELDS):
                setattr(py_instance, name.lower(), db_instance[rank])
            py_instances.append(py_instance)
        return py_instances

class Video(DatabaseItem):
    TABLE = "VIDEO"
    FIELDS = [
        "VIDEO_ID", "TITLE", "SEARCH_TITLE", "RELEASE_DATE", "PATH", "DETAIL_POSTER",
        "THUMBNAIL", "RUNTIME", "RATING",
        "RESOLUTION", "WIDTH", "HEIGHT", "ASPECT_RATIO", "PARENTAL_RATING", "SYSTEM",
        "VIDEO_CODEC", "FPS", "SIZE", "PLAY_COUNT", "SHOW_ID", "EPISODE", "CREATE_TIME",
        "WALLPAPER", "CAST_ID", "UPDATE_STATE", "MOUNT_DEVICE_ID", "SEASON", "TTID", "TITLE_TYPE",
        "VIDEO1", "VIDEO2", "VIDEO3", "VIDEO4", "VIDEO5"]

    def __str__(self):
        return "%s : %s (%s %s)" % (self.path, self.title, self.season, self.episode)

class Show(DatabaseItem):
    TABLE = "SHOW"
    FIELDS = [
        "SHOW_ID", "TITLE", "SEARCH_TITLE", "LAST_PLAY_ITEM", "TOTAL_ITEM", "RELEASE_DATE",
        "DETAIL_POSTER", "THUMBNAIL", "RATING", "RESOLUTION", "WIDTH", "HEIGHT",
        "PARENTAL_RATING", "SYSTEM", "VIDEO_CODEC", "FPS", "CREATE_TIME", "SHOW_TYPE",
        "WALLPAPER", "CAST_ID", "UPDATE_STATE", "TTID", "TOTAL_EPISODES", "SHOW1", "SHOW2",
        "SHOW3", "SHOW4", "SHOW5",
    ]

    def __str__(self):
        return "%s (%s)" % (self.title, self.show_id)


def select(cursor, request, *args):
    return cursor.execute(request, args).fetchall()

def get_all_table_names(cursor):
    return select(cursor, """SELECT name FROM sqlite_master
    WHERE type='table'
    ORDER BY name;
    """)

def get_DB_version(cursor):
    return select(cursor, """SELECT version FROM DB_VERSION;""")


def get_table_details(cursor, table_name):
    return select(cursor, """SELECT * FROM sqlite_master
    WHERE type='table'
    AND name=?;
    """, table_name)

connection = sqlite3.connect("/mnt/externalDrive/nmj_database/media.db")
cursor = connection.cursor()

print get_all_table_names(cursor)
#fields = get_table_details(cursor, "SHOW")[0][4].split("(")[1][:-1].split(", ")
#print "".join(['"%s", '%field.split(" ")[0] for field in fields])
print get_DB_version(cursor)

#print select(cursor, "SELECT TITLE, TOTAL_ITEM, SHOW_TYPE, TOTAL_EPISODES FROM SHOW")

videos = Video.load(cursor)
for video in videos:
	if "Naruto" in video.path:
		print video.title, video.path


shows = Show.load(cursor)
for show in shows:
	print show.title, show.show_type
#
#cursor.execute("""UPDATE SHOW SET TOTAL_ITEM=12 WHERE SHOW_ID=36;""")
connection.commit()



